using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Diagnostics;
using System.Web;
using System.Configuration;

namespace OvernightForRecaluateQty
{
    public class DBAccess
    {
        public static string mConnectionString = "";
        private SqlConnection mCon = null;
        private SqlDataAdapter mAdapter = null;
        private SqlCommand mCom = null;
        private SqlTransaction mTran = null;
        private static string mstrSqlLogFilePath = "";
        private static bool mbIsWriteSqlScriptLog = false;

        //Whether need to create Transaction
        private bool mIsHaveTransaction = false;
        //Whether auto close database connection runed a mothed
        private bool mIsAutoClose = false;
        private static int m_iKeepMsgTimes = 0;

        public static int KeepMsgTimes
        {
            get
            {
                return m_iKeepMsgTimes;
            }
            set
            {
                m_iKeepMsgTimes = value;
            }
        }

        /// <summary>
        /// Init DBAccess class
        /// </summary>
        /// <param name="isHaveTransaction">Whether need to create a transaction </param>
        public DBAccess(bool isAutoClose, bool isHaveTransaction)
        {
            mIsHaveTransaction = isHaveTransaction;
            mIsAutoClose = isAutoClose;

            OpenCon();
            mCom = new SqlCommand();
            mCom.Connection = mCon;
            mCom.CommandTimeout = 300;

            if (mIsHaveTransaction)
            {
                mTran = mCon.BeginTransaction();
                mCom.Transaction = mTran;
            }
        }

        /// <summary>
        /// default isAutoClose is true, isHaveTransaction is false
        /// </summary>
        public DBAccess()
        {
            mIsAutoClose = true;
            OpenCon();
            mCom = new SqlCommand();
            mCom.Connection = mCon;
        }

        /// <summary>
        /// Whether need to create a transaction
        /// </summary>
        public bool IsHaveTransaction
        {
            get
            {
                return mIsHaveTransaction;
            }
            set
            {
                mIsHaveTransaction = value;
            }
        }
        /// <summary>
        /// Whether auto close data connection
        /// </summary>
        public bool IsAutoClose
        {
            get
            {
                return mIsAutoClose;
            }
            set
            {
                mIsAutoClose = value;
            }
        }

        public static string SqlLogFilePath
        {
            get
            {
                return mstrSqlLogFilePath;
            }
            set
            {
                mstrSqlLogFilePath = value;
            }
        }

        public static bool IsWriteSqlScriptLog
        {
            get
            {
                return mbIsWriteSqlScriptLog;
            }
            set
            {
                mbIsWriteSqlScriptLog = value;
            }
        }
        /// <summary>
        /// get or set a connection string
        /// </summary>
        /// 
        public static string ConnectionDBString
        {
            get
            {
                return mConnectionString;
            }
            set
            {
                mConnectionString = value;
            }
        }

        /// <summary>
        /// get or set a SqlConnection,if SqlConnection is not existing or close,create a sqlconnection or open it
        /// </summary>
        public SqlConnection Con
        {
            get
            {
                return mCon;
            }
            set
            {
                mCon = value;
            }
        }

        /// <summary>
        /// get or set a SqlCommand
        /// </summary>
        public SqlCommand Com
        {
            get
            {
                return mCom;
            }
            set
            {
                mCom = value;
            }
        }

        /// <summary>
        ///Execute multi sql,and return nothing
        /// </summary>
        /// <param name="SqlList">include multi sql</param>
        public void ExecuteSql(ArrayList SqlList)
        {
            string sql = "";

            for (int i = 0; i < SqlList.Count; i++)
            {
                sql = SqlList[i].ToString();
                WriteSqlLog(sql);
                mCom.Parameters.Clear();
                mCom.CommandText = sql;
                mCom.ExecuteNonQuery();
            }

            AutoCloseCon();
        }

        /// <summary>
        /// Execute single sql,and return nothing
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <returns></returns>
        public void ExecuteSql(string sql)
        {
            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;

            mCom.ExecuteNonQuery();
            AutoCloseCon();
        }

        /// <summary>
        /// Execute single sql use paramenter
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        public void ExecuteSql(string sql, SqlParameter[] commandParameters)
        {
            WriteSqlLog(sql);
            mCom.CommandText = sql;
            mCom.CommandType = CommandType.Text;
            mCom.Parameters.Clear();
            foreach (SqlParameter parm in commandParameters)
            {
                mCom.Parameters.Add(parm);
            }
            mCom.ExecuteNonQuery();
            mCom.Parameters.Clear();
            AutoCloseCon();
        }


        /// <summary>
        /// Execute single sql,and return SqlDataReader
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader GetDataReader(string sql)
        {
            SqlDataReader sqlDataReader;

            WriteSqlLog(sql);
            if (mCom == null)
            {
                mCom = new SqlCommand();
                mCom.Connection = mCon;
            }
            mCom.Parameters.Clear();
            mCom.CommandText = sql;

            sqlDataReader = mCom.ExecuteReader();

            AutoCloseCon();

            return sqlDataReader;
        }

        /// <summary>
        /// Execture a sql,return a bool type value
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <returns></returns>
        public bool GetBooleanValue(string sql)
        {
            object oRet = null;
            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            oRet = mCom.ExecuteScalar();

            AutoCloseCon();
            if (oRet == null || oRet == System.DBNull.Value)
                return false;
            else
                return bool.Parse(oRet.ToString());
        }

        /// <summary>
        /// Execture a sql,return a Double type value
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <returns></returns>
        public double GetDoubleValue(string sql)
        {
            object oRet = null;
            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;

            oRet = mCom.ExecuteScalar();

            AutoCloseCon();
            if (oRet == null || oRet == System.DBNull.Value)
                return 0;
            else
                return double.Parse(oRet.ToString());
        }

        /// <summary>
        /// Execture a sql,return a float type value
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <returns></returns>
        public float GetFloatValue(string sql)
        {
            object oRet = null;

            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            oRet = mCom.ExecuteScalar();

            AutoCloseCon();
            if (oRet == null || oRet == System.DBNull.Value)
                return 0f;
            else
                return float.Parse(oRet.ToString());
        }

        /// <summary>
        /// Execture a sql,return a float type value
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <returns></returns>
        public int GetIntValue(string sql)
        {
            object oRet = null;
            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            oRet = mCom.ExecuteScalar();

            AutoCloseCon();
            if (oRet == null || oRet == System.DBNull.Value)
                return 0;
            else
                return int.Parse(oRet.ToString());
        }
        /// <summary>
        /// Execture a sql,return a string type value
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <returns></returns>
        public object GetObjectValue(string sql)
        {
            object oRet = null;
            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            oRet = mCom.ExecuteScalar();

            AutoCloseCon();

            return oRet;
        }


        /// <summary>
        /// Execture a sql,return a string type value
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns></returns>
        public string GetValue(string sql, SqlParameter[] commandParameters)
        {
            string sRet = "";
            object oRet = null;
            WriteSqlLog(sql);

            mCom.CommandText = sql;
            mCom.CommandType = CommandType.Text;
            mCom.Parameters.Clear();
            foreach (SqlParameter parm in commandParameters)
            {
                mCom.Parameters.Add(parm);
            }
            oRet = mCom.ExecuteScalar();
            mCom.Parameters.Clear();

            if (oRet == null || oRet == System.DBNull.Value)
            {
                sRet = null;
            }
            else
            {
                sRet = oRet.ToString();
            }
            AutoCloseCon();

            return sRet;
        }
        /// <summary>
        /// Execture a sql,return a string type value
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <returns></returns>
        public string GetValue(string sql)
        {
            string sRet = "";
            object oRet = null;
            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            oRet = mCom.ExecuteScalar();

            if (oRet == null || oRet == System.DBNull.Value)
            {
                sRet = null;
            }
            else
            {
                sRet = oRet.ToString();
            }
            AutoCloseCon();

            return sRet;
        }

        /// <summary>
        /// Execture a sql return a datatable
        /// </summary>
        /// <param name="sql">Input Sql</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        public DataTable GetDataTable(string sql, SqlParameter[] commandParameters)
        {
            DataSet dataSet = new DataSet();
            WriteSqlLog(sql);
            mCom.CommandText = sql;
            if (mAdapter == null)
            {
                mAdapter = new SqlDataAdapter();
            }
            mCom.CommandType = CommandType.Text;
            mCom.Parameters.Clear();
            foreach (SqlParameter parm in commandParameters)
            {
                mCom.Parameters.Add(parm);
            }
            mAdapter.SelectCommand = mCom;
            mAdapter.Fill(dataSet);

            mCom.Parameters.Clear();

            AutoCloseCon();

            if (dataSet == null)
            {
                return null;
            }
            else
            {
                return dataSet.Tables[0];
            }
        }

        /// <summary>
        /// Execture a sql return a datatable
        /// </summary>
        /// <param name="sql">Input Sql</param>
        public DataTable GetDataTable(string sql)
        {
            DataSet dataSet = new DataSet();
            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            if (mAdapter == null)
            {
                mAdapter = new SqlDataAdapter();
            }
            mAdapter.SelectCommand = mCom;
            mAdapter.Fill(dataSet);

            AutoCloseCon();

            if (dataSet == null || dataSet.Tables.Count == 0)
            {
                return null;
            }
            else
            {
                return dataSet.Tables[0];
            }
        }


        /// <summary>
        /// Execture a sql return a dataset with default table name
        /// </summary>
        /// <param name="sql">Input Sql</param>
        public DataSet GetDataSet(string sql)
        {
            DataSet dataSet = new DataSet();

            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            if (mAdapter == null)
            {
                mAdapter = new SqlDataAdapter();
            }
            mAdapter.SelectCommand = mCom;
            mAdapter.Fill(dataSet);

            AutoCloseCon();

            return dataSet;
        }
        public DataRow GetDataFirstRow(string sql)
        {
            DataSet dataSet = new DataSet();

            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            if (mAdapter == null)
            {
                mAdapter = new SqlDataAdapter();
            }
            mAdapter.SelectCommand = mCom;
            mAdapter.Fill(dataSet);

            AutoCloseCon();
            if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return dataSet.Tables[0].Rows[0];
            }


        }

        /// <summary>
        /// Execture a sql return a dataset with a table name
        /// </summary>
        /// <param name="sql">input sql</param>
        /// <param name="TableName">Table name in dataset </param>
        /// <param name="dataSet">Fill the data in the dataset</param>
        public void GetDataSet(string sql, string TableName, DataSet dataSet)
        {
            WriteSqlLog(sql);
            mCom.Parameters.Clear();
            mCom.CommandText = sql;
            if (mAdapter == null)
            {
                mAdapter = new SqlDataAdapter();
            }
            mAdapter.SelectCommand = mCom;
            mAdapter.Fill(dataSet, TableName);

            AutoCloseCon();
        }

        /// <summary>
        /// Execute multi sql ,and return multi data
        /// </summary>
        /// <param name="SqlList">sql list</param>
        /// <returns></returns>
        

        public void SaveBinaryInfo(string strTableName, string strFieldName, byte[] btFieldValue, string strCondition)
        {
            DataSet ds = new DataSet(strTableName);
            string strSql = "";

            strSql = "Select * From " + strTableName + " Where " + strCondition;
            mCom.CommandText = strSql;

            if (mAdapter == null)
            {
                mAdapter = new SqlDataAdapter();
            }
            mAdapter.SelectCommand = mCom;
            SqlCommandBuilder cb = new SqlCommandBuilder(mAdapter);
            mAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            mAdapter.Fill(ds, strTableName);

            if (ds.Tables[0].Rows.Count == 1)
            {
                ds.Tables[0].Rows[0][strFieldName] = btFieldValue;
            }
            mAdapter.Update(ds, strTableName);
        }

        public void CloseCon()
        {


            if (mCom != null)
            {
                try
                {

                    mCom.Cancel();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
                mCom.Dispose();
                mCom = null;
            }

            if (mAdapter != null)
            {
                mAdapter.Dispose();
                mAdapter = null;
            }


            if (mCon != null && mCon.State == ConnectionState.Open)
            {
                mCon.Close();
            }


            if (mCon != null)
            {
                mCon.Dispose();
                mCon = null;
            }
        }


        /// <summary>
        /// Close SqlConnection and release resources
        /// </summary>
        public void CommitTranAndCloseCon()
        {
            CommitTran();

            CloseCon();
            DBAccess.KeepMsgTimes = 0;
        }

        private void AutoCloseCon()
        {
            if (mIsAutoClose)
            {
                CommitTranAndCloseCon();
            }
        }
        /// <summary>
        /// Open a SqlConnection ,if SqlConnection is existed ,and do nothing
        /// </summary>
        public void OpenCon()
        {
            mCon = new SqlConnection("");
            mCon.ConnectionString = mConnectionString;

            mCon.Open();
        }

        public void OpenCon(string strDBname, string strServerName)
        {
            CloseCon();

            mCon = new SqlConnection();
            mCon.ConnectionString = GetInterDBConnection(strDBname, strServerName);
            mCon.Open();
        }
        private string GetInterDBConnection(string strDBname, string strServerName)
        {
            string strConn = mConnectionString;

            int i0 = strConn.IndexOf("Initial Catalog=", 0, StringComparison.CurrentCultureIgnoreCase);
            if (i0 <= 0) return "";

            int i1 = strConn.IndexOf(":", i0, StringComparison.CurrentCultureIgnoreCase);
            if (i1 <= 0) return "";

            int j0 = strConn.IndexOf("Data Source=", 0, StringComparison.CurrentCultureIgnoreCase);
            if (j0 <= 0) return "";

            int j1 = strConn.IndexOf(":", j0, StringComparison.CurrentCultureIgnoreCase);
            if (j1 <= 0) return "";

            string strDB = strConn.Substring(i0, i1 - i0);
            string strServer = strConn.Substring(j0, j1 - j0);
            strConn = strConn.Replace(strDB, "Initial Catalog=" + strDBname);
            strConn = strConn.Replace(strServer, "Data Source=" + strServerName);

            return strConn;
        }

        /// <summary>
        /// Commit a existed transaction
        /// </summary>
        public void CommitTran()
        {
            if (mTran != null)
            {
                mTran.Commit();
                mTran = null;
            }
            DBAccess.KeepMsgTimes = 0;
        }
        /// <summary>
        /// Create a transation
        /// </summary>
        public void BeginTran()
        {
            if (mTran == null)
            {
                mTran = mCon.BeginTransaction();
                mCom.Transaction = mTran;
            }
        }
        /// <summary>
        /// Rollback transaction
        /// </summary>
        public void RollbackTran()
        {

            if (mTran != null)
            {

                mTran.Rollback();
                mTran = null;
            }
            DBAccess.KeepMsgTimes = 0;
        }
        /// <summary>
        /// Rollback transaction
        /// </summary>
        public void RollbackTranAndClose()
        {
            if (mTran != null)
            {
                mTran.Rollback();
                mTran = null;
            }
            CloseCon();
            DBAccess.KeepMsgTimes = 0;
        }
        //Wtite sql Log
        private void WriteSqlLog(string strSql)
        {

            if (mbIsWriteSqlScriptLog)
            {
                string strPath = "";
                string strDate = DateTime.Now.ToString();

                strPath = "SqlLog" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0') + DateTime.Now.Day.ToString().PadLeft(2, '0') + ".txt";
                if (mstrSqlLogFilePath != "")
                {
                    if (mstrSqlLogFilePath.Substring(mstrSqlLogFilePath.Length - 1, 1) == "\\")
                    {
                        strPath = mstrSqlLogFilePath + strPath;
                    }
                    else
                    {
                        strPath = mstrSqlLogFilePath + "\\" + strPath;
                    }
                }
                try
                {
                    if (!File.Exists(strPath))
                    {
                        using (StreamWriter sw = File.CreateText(strPath))
                        {
                            sw.WriteLine("Sql Script Log: ");
                        }
                    }

                    using (StreamWriter sw = File.AppendText(strPath))
                    {
                        sw.WriteLine(strDate + "\n" + strSql + "\n");
                    }
                }
                catch (IOException e)
                {
                    string strMsg = "The write operation could not be performed because the specified part of the file is locked.";


                }
            }
        }
        public bool InTran
        {
            get
            {
                return mTran != null;
            }

        }
    }
}
